import prisma from "../config/database";
import { getNextMonday } from "../utils/date.util";

// interface WishDishResult {
//   id: number | bigint; // 可能为 BigInt
//   name: string;
//   description: string | null;
//   picture: string | null;
//   thumbnail: string | null;
//   category_id: number | bigint;
//   avg_rating: number;
//   rating_count: number;
//   total_score: number;
//   created_at: Date;
//   updated_at: Date;
//   category_name: string;
//   wishes_count: number;
//   is_my_wish: boolean;
// }

export class WishService {
  /**
   * 查询所有用户下周（下周一日期）想吃的菜品列表
   * 在菜品中增加wishesCount字段，代表该菜品被多少用户下周想吃
   */
  public static async listAll() {
    // 获取下周一日期
    const nextMonday = new Date(getNextMonday());

    // 查询所有用户下周（下周一日期）想吃的菜品列表
    const wishlist = await prisma.wish.groupBy({
      by: ["dishesId"],
      where: {
        date: nextMonday,
      },
      _count: { userId: true },
      orderBy: {
        _count: { userId: "desc" },
      },
    });

    const dishesIds = wishlist.map((item) => item.dishesId);
    const list = await prisma.dishes.findMany({
      where: {
        id: {
          in: dishesIds,
        },
      },
      include: {
        category: true,
      },
    });

    // 转换为前端需要的格式
    const dishesList = list.map((dishes) => {
      const { category, ...rest } = dishes;
      const wishesCount =
        wishlist.find((e) => e.dishesId === dishes.id)?._count.userId || 0;

      return {
        ...rest,
        wishesCount,
        categoryName: category.name,
        description: dishes.description || "",
        picture: dishes.picture || "",
        thumbnail: dishes.thumbnail || "",
      };
    });
    return dishesList;
  }

  /**
   * 查询所有用户下周（下周一日期）想吃的菜品列表
   * 在菜品中增加wishesCount字段，代表该菜品被多少用户下周想吃
   * 在菜品中增加isWished字段，代表该菜品是否被用户下周想吃
   */

  public static async listAllWithIsWished(userId: number) {
    //   const nextMonday = new Date(getNextMonday());
    //   const result: WishDishResult[] = await prisma.$queryRaw`
    //     SELECT
    //       d.*,
    //       c."name" AS category_name,
    //       COUNT(d."id") AS wishes_count,
    //       CASE
    //         WHEN uw."id" IS NOT NULL THEN
    //           TRUE
    //         ELSE
    //           FALSE
    //       END AS is_my_wish
    //     FROM
    //       wish w
    //       LEFT JOIN dishes d ON d."id" = w.dishes_id
    //       LEFT JOIN category c ON c."id" = d.category_id
    //       LEFT JOIN wish uw ON uw.dishes_id = d."id"
    //       AND uw."date" = ${nextMonday}
    //       AND uw.user_id = ${userId}
    //     WHERE
    //       w."date" = ${nextMonday}
    //     GROUP BY
    //       d."id",
    //       c."name",
    //       uw."id"
    //     ORDER BY
    //       wishes_count DESC
    // `;

    //   const serializedResult = result.map((item) => {
    //     const converted: Record<string, any> = {};
    //     for (const [key, value] of Object.entries(item)) {
    //       const fieldsMap = {
    //         category_id: "categoryId",
    //         avg_rating: "avgRating",
    //         rating_count: "ratingCount",
    //         total_score: "totalScore",
    //         created_at: "createdAt",
    //         updated_at: "updatedAt",
    //         category_name: "categoryName",
    //         wishes_count: "wishesCount",
    //         is_my_wish: "isWished",
    //       };

    //       const newKey = (fieldsMap as Record<string, any>)[key] || key;

    //       // 若字段是 BigInt，转换为 Number（值在安全范围内）或 String
    //       converted[newKey] = typeof value === "bigint" ? Number(value) : value;
    //     }
    //     return converted;
    //   });

    //   return serializedResult;

    // 获取下周一日期
    const nextMonday = new Date(getNextMonday());

    // 查询所有用户下周（下周一日期）想吃的菜品列表
    const wishlist = await prisma.wish.groupBy({
      by: ["dishesId"],
      where: {
        date: nextMonday,
      },
      _count: { userId: true },
      orderBy: {
        _count: { userId: "desc" },
      },
    });
    // return wishlist;

    const dishesIds = wishlist.map((item) => item.dishesId);
    const list = await prisma.dishes.findMany({
      where: {
        id: {
          in: dishesIds,
        },
      },
      include: {
        category: { select: { name: true } },
        wishes: {
          where: { date: nextMonday, userId: userId },
          select: { userId: true },
        },
      },
    });
    // return list;

    // 转换为前端需要的格式
    const dishesList = list.map((dish) => {
      const { category, wishes, ...rest } = dish;

      return {
        ...rest,
        categoryName: category.name,
        wishesCount:
          wishlist.find((e) => e.dishesId === dish.id)?._count.userId || 0,
        isWished: wishes.length > 0,
      };
    });

    // const dishesList = wishlist.map((wish, i) => {
    //   const dish = list.find((d) => d.id === wish.dishesId);
    //   if (dish) {
    //     const { category, wishes, ...rest } = dish;

    //     return {
    //       ...rest,
    //       categoryName: category.name,
    //       wishesCount: wish._count.userId,
    //       isWished: wishes.length > 0,
    //     };
    //   } else {
    //     // 菜品删除后没有删除wish表中的记录
    //     return { name: "菜品已被删除" };
    //   }
    // });
    dishesList.sort((a, b) => {
      return b.wishesCount - a.wishesCount;
    });
    return dishesList;
  }

  /**
   * 查询用户下周想吃的菜品列表
   */
  public static async list(userId: number) {
    const nextMonday = new Date(getNextMonday());
    const wishList = await prisma.wish.findMany({
      where: {
        userId,
        date: nextMonday,
      },
      include: {
        dishes: {
          include: { category: true },
        },
      },
    });

    //从想吃表中取出所有菜品并加上分类名称
    const dishList = wishList.map((wish) => {
      const { dishes } = wish;
      const { category, ...rest } = dishes;
      return {
        ...rest,
        categoryName: category.name,
        description: rest.description || "",
        picture: rest.picture || "",
        thumbnail: rest.thumbnail || "",
      };
    });

    return dishList;
  }

  /**
   * 创建/修改用户（下周）想吃的菜品
   */
  public static async upsert(userId: number, dishesIdList: number[]) {
    // 开起事务
    const wishList = await prisma.$transaction(async (tx) => {
      // 获取下周一的日期
      const nextMonday = new Date(getNextMonday());

      // 先删除该用户下周一的旧记录
      await tx.wish.deleteMany({
        where: {
          userId,
          date: nextMonday,
        },
      });

      // 批量创建新记录
      await tx.wish.createMany({
        data: dishesIdList.map((dishId) => ({
          userId,
          dishesId: dishId,
          date: nextMonday,
        })),
      });

      // 查询用户下周想吃的菜品列表
      return tx.wish.findMany({
        where: {
          userId,
          date: nextMonday,
        },
        include: {
          dishes: {
            include: { category: true },
          },
        },
      });
    });

    //从想吃表中取出所有菜品并加上分类名称
    const dishList = wishList.map((wish) => {
      const { dishes } = wish;
      const { category, ...rest } = dishes;
      return {
        ...rest,
        categoryName: category.name,
        description: rest.description || "",
        picture: rest.picture || "",
        thumbnail: rest.thumbnail || "",
      };
    });

    return dishList;
  }

  /**
   * 添加菜品到下周想吃
   */
  public static async add(dishesId: number, userId: number) {
    const nextMonday = new Date(getNextMonday());

    let wish = await prisma.wish.findFirst({
      where: {
        userId,
        dishesId,
        date: nextMonday,
      },
    });

    if (wish) {
      return wish;
    }

    wish = await prisma.wish.create({
      data: {
        userId,
        dishesId,
        date: nextMonday,
      },
    });

    return wish;
  }

  /**
   * 删除用户下周想吃的菜品
   */
  public static remove(dishesId: number, userId: number) {
    const nextMonday = new Date(getNextMonday());

    return prisma.wish.deleteMany({
      where: {
        userId,
        dishesId,
        date: nextMonday,
      },
    });
  }
}
